import pymysql
import asyncio

db_2m = {
    'host': 'rm-2zeqn1m3b6ik438mz.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'ppg'  # 数据库名
}

db_wm = {
    'host': 'rm-2zev27ct5v8p9ssnyvo.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'nft'  # 数据库名
}

async def main():
    # 连接2m库
    connection_2m = pymysql.connect(**db_2m)
    # 连接wm库
    connection_wm = pymysql.connect(**db_wm)
    # 创建游标对象
    cursor_2m = connection_2m.cursor()
    cursor_wm = connection_wm.cursor()
    # 选择要导入的表名
    table_name = "t_sku_sync"
    try:
        # 获取2m库中的表结构
        cursor_2m.execute(f"DESCRIBE {table_name};")
        table_structure = cursor_2m.fetchall()
        # 从2m库中选择所有数据
        cursor_2m.execute(f"select spu_no,max(`ct`),max(`no`),max(`name`),max(`spu_name`),max(`id_no`),max(`id_name`),max(`bind_sku_no`),max(`owner_no`),max(`is_deleted`),max(`spu_type`),max(`idx`),max(`status`),max(`chain_status`),max(`allocate_time`),max(`buy_price`),max(`token_id`),max(`tx_hash`),max(`contract_address`),max(`block_chain`),max(`contract_standard`),max(`chain_flag`),max(`trade_flag`),max(`withdraw_flag`),max(`meta`),max(`remark`),max(`brand_no`),max(`activity_no`),max(`slot_no`),max(`ext`),max(`tenant`),CONCAT('https://ppg-bj-public.oss-cn-beijing.aliyuncs.com/',(select remote_name from t_attachment where `no` = (select attachment_no from t_media where type = '01' and biz_no=t_sku.spu_no LIMIT 1))) from t_sku where trade_flag = 1 and `status` !='102' and brand_no='BR20220530000001' GROUP BY spu_no;")
        rows = cursor_2m.fetchall()

        # 插入数据到wm库中的表
        insert_query = f"INSERT INTO {table_name} VALUES ("
        for column in table_structure:
            insert_query += "%s, "
        insert_query = insert_query.rstrip(", ") + ");"
        cursor_wm.execute("DELETE FROM t_sku_sync;")
        cursor_wm.executemany(insert_query, rows)

        # 提交事务
        connection_wm.commit()
        print("数据表导入成功！")
    except (pymysql.Error) as error:
        print("导入数据表时发生错误:", error)

    # 关闭游标和连接
    cursor_2m.close()
    cursor_wm.close()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())